import streamlit as st
import sqlite3
from sqlite3 import Error


# 初始化数据库连接
def create_connection(db_file):
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        st.error(e)
    return conn


# 创建表
def create_table(conn, create_table_sql):
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
        conn.commit()
    except Error as e:
        st.error(e)


# 获取所有表名
def get_table_names(conn):
    try:
        c = conn.cursor()
        c.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = c.fetchall()
        return [table[0] for table in tables]
    except Error as e:
        st.error(e)
        return []


# 获取表的列信息
def get_table_columns(conn, table_name):
    try:
        c = conn.cursor()
        c.execute(f"PRAGMA table_info({table_name});")
        columns = c.fetchall()
        return columns
    except Error as e:
        st.error(e)
        return []


# 获取表的数据
def get_table_data(conn, table_name):
    try:
        c = conn.cursor()
        c.execute(f"SELECT * FROM {table_name};")
        data = c.fetchall()
        return data
    except Error as e:
        st.error(e)
        return []


# 插入数据
def insert_data(conn, table_name, data):
    try:
        c = conn.cursor()
        placeholders = ', '.join(['?'] * len(data))
        c.execute(f"INSERT INTO {table_name} VALUES ({placeholders});", data)
        conn.commit()
    except Error as e:
        st.error(e)


# 更新数据
def update_data(conn, table_name, set_clause, condition):
    try:
        c = conn.cursor()
        c.execute(f"UPDATE {table_name} SET {set_clause} WHERE {condition};")
        conn.commit()
    except Error as e:
        st.error(e)


# 删除数据
def delete_data(conn, table_name, condition):
    try:
        c = conn.cursor()
        c.execute(f"DELETE FROM {table_name} WHERE {condition};")
        conn.commit()
    except Error as e:
        st.error(e)


# 删除表
def drop_table(conn, table_name):
    try:
        c = conn.cursor()
        c.execute(f"DROP TABLE IF EXISTS {table_name};")
        conn.commit()
    except Error as e:
        st.error(e)


# 创建数据库连接
db_file = "example.db"
conn = create_connection(db_file)

# 主界面
st.title("SQLite 数据库管理系统")

# 选择操作
operation = st.sidebar.selectbox("选择操作",
                                 ["查看表列表", "创建表", "查看表内容", "插入数据", "更新数据", "删除数据", "删除表"])

if operation == "查看表列表":
    st.subheader("表列表")
    tables = get_table_names(conn)
    st.write(tables)

elif operation == "创建表":
    st.subheader("创建表")
    table_name = st.text_input("表名")
    st.write("### 列定义")
    num_columns = st.number_input("列的数量", min_value=1, value=1, step=1)
    columns = []
    for i in range(num_columns):
        st.write(f"### 列 {i + 1}")
        col_name = st.text_input(f"列名 {i + 1}", key=f"col_name_{i}")
        col_type = st.selectbox(f"列类型 {i + 1}", ["INTEGER", "TEXT", "REAL", "BLOB", "NULL"], key=f"col_type_{i}")
        is_primary_key = st.checkbox(f"主键 {i + 1}", key=f"is_primary_key_{i}")
        columns.append((col_name, col_type, is_primary_key))

    if st.button("创建表"):
        create_table_sql = f"CREATE TABLE IF NOT EXISTS {table_name} ("
        for col_name, col_type, is_primary_key in columns:
            create_table_sql += f"{col_name} {col_type}"
            if is_primary_key:
                create_table_sql += " PRIMARY KEY"
            create_table_sql += ", "
        create_table_sql = create_table_sql.rstrip(", ") + ");"
        create_table(conn, create_table_sql)
        st.success(f"表 {table_name} 创建成功")

elif operation == "查看表内容":
    st.subheader("查看表内容")
    tables = get_table_names(conn)
    table_name = st.selectbox("选择表", tables)
    if st.button("查看"):
        columns = get_table_columns(conn, table_name)
        data = get_table_data(conn, table_name)
        st.write("列信息:")
        st.write(columns)
        st.write("数据:")
        st.dataframe(data)

elif operation == "插入数据":
    st.subheader("插入数据")
    tables = get_table_names(conn)
    table_name = st.selectbox("选择表", tables)
    columns = get_table_columns(conn, table_name)
    data = []
    for col in columns:
        col_name = col[1]
        col_type = col[2]
        if col_type.upper() == "INTEGER":
            value = st.number_input(f"输入 {col_name} ({col_type})", value=0, key=f"insert_{col_name}")
        else:
            value = st.text_input(f"输入 {col_name} ({col_type})", key=f"insert_{col_name}")
        data.append(value)
    if st.button("插入"):
        insert_data(conn, table_name, data)
        st.success("数据插入成功")

elif operation == "更新数据":
    st.subheader("更新数据")
    tables = get_table_names(conn)
    table_name = st.selectbox("选择表", tables)
    columns = get_table_columns(conn, table_name)
    set_clause = st.text_input("设置子句 (例如: name = 'John')")
    condition = st.text_input("条件 (例如: id = 1)")
    if st.button("更新"):
        update_data(conn, table_name, set_clause, condition)
        st.success("数据更新成功")

elif operation == "删除数据":
    st.subheader("删除数据")
    tables = get_table_names(conn)
    table_name = st.selectbox("选择表", tables)
    condition = st.text_input("条件 (例如: id = 1)")
    if st.button("删除"):
        delete_data(conn, table_name, condition)
        st.success("数据删除成功")

elif operation == "删除表":
    st.subheader("删除表")
    tables = get_table_names(conn)
    table_name = st.selectbox("选择表", tables)
    if st.button("删除表"):
        drop_table(conn, table_name)
        st.success(f"表 {table_name} 删除成功")

# 关闭数据库连接
if conn:
    conn.close()
